package com.wei.util;

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.stereotype.Component;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import com.wei.constant.ExcelConstants;
import com.wei.entity.Information;
import com.wei.service.InformationService;
import com.wei.strategy.CustomCellStyleHandler;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Component
public class ExcelUtils {
	
	
    @Resource
    private InformationService informationService;
    
    /**
     * 导出逻辑代码
     *
     * @param response
     */
    public void dataExport300w(HttpServletResponse response) {
        OutputStream outputStream = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println("导出开始时间:"+ startTime);
            outputStream = response.getOutputStream();
            WriteWorkbook writeWorkbook = new WriteWorkbook();
            writeWorkbook.setOutputStream(outputStream);
            writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
            ExcelWriter writer = new ExcelWriter(writeWorkbook);
            String fileName = new String(("到处百万数据-easyexcel测试").getBytes(), StandardCharsets.UTF_8);
            // TODO WriteTable 标题这块可以作为公共的封装起来：通过反射获取变量上注解等
            WriteTable table = new WriteTable();
            ArrayList<WriteHandler> customWriteHandlerList = new ArrayList<WriteHandler>();
//            customWriteHandlerList.add(new AutoHeadColumnWidthStyleStrategy());
            // easyexcel的列宽不准，但是行高是准确的，列宽往往比设置的要小
            customWriteHandlerList.add(new SimpleColumnWidthStyleStrategy(20));
            customWriteHandlerList.add(new SimpleRowHeightStyleStrategy((short) 18, (short)14));
            customWriteHandlerList.add(new CustomCellStyleHandler());
            table.setCustomWriteHandlerList(customWriteHandlerList);
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Collections.singletonList("网站号"));
            titles.add(Collections.singletonList("大神昵称"));
            titles.add(Collections.singletonList("进站时间"));
            titles.add(Collections.singletonList("所属大神团"));
            titles.add(Collections.singletonList("订单数量"));
            titles.add(Collections.singletonList("收入"));
            titles.add(Collections.singletonList("月份"));
            table.setHead(titles);
            // 记录总数：实际中需要根据查询条件（过滤数据）进行统计即可，
            // TODO 此处写入限定的条数进行自测
            Integer totalCount = informationService.count();
            // 每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            // 每次写入的数据量20w
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            // 计算需要的Sheet数量
            int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            // 计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            int oneSheetWriteCount = totalCount > sheetDataRows ? sheetDataRows / writeDataRows : totalCount % writeDataRows > 0 ? totalCount / writeDataRows + 1 : totalCount / writeDataRows;
            // 计算最后一个sheet需要写入的次数
            int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));

            // 开始分批查询分次写入
            // 注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
            List<List<String>> dataList = new ArrayList<>();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetNo(i);
                sheet.setSheetName("测试" + i);
                // 循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 || i==0 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                	/**
                	 * i 0 1 
                	 * j 0 1 2 3 4
                	 * 0 20w 40w 60w 80w 100w 120w
                	 * 20w * 1 + 
                	 */
                	System.out.println("oneSheetWriteCount=="+((j * writeDataRows) + (sheetDataRows * i)));
                    // 集合复用,便于GC清理
                    dataList.clear();
                    // 分页查询一次20w
                    List<Information> resultList = informationService.exportBy100w(((j * writeDataRows) + (sheetDataRows * i)), writeDataRows);
                    if (!CollectionUtils.isEmpty(resultList)) {
                        resultList.forEach(item -> {
                        	dataList.add(Arrays.asList(item.getNetNumber(),item.getNickName(),sdf.format(item.getSignTime()),item.getBelongOrg(),item.getOrderNum().toString(),item.getIncome().toString(),item.getMonth()+""));
                        });
                    }
                    // 写数据
                    writer.write(dataList, sheet, table);
                }
            }

            // 下载EXCEL 以下代码可以作为公共的进行封装.
            setExcelRespProp(response, fileName);
            writer.finish();
            outputStream.flush();
            // 导出时间结束
            long endTime = System.currentTimeMillis();
            System.out.println("导出结束时间:"+ endTime + "ms");
            System.out.println("导出所用时间:"+ (endTime - startTime) / 1000 + "秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
    
    /**
     * 设置excel下载响应头属性
     */
    public static void setExcelRespProp(HttpServletResponse response, String rawFileName){
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = null;
        try {
            fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        } catch (UnsupportedEncodingException e) {
            System.out.println("设置excel下载响应头属性，失败 "+ e.getMessage());
        }
        response.setHeader("Content-disposition", "attachment;filename=utf-8''" + fileName + ".xlsx");
    }
}
